<?php 
$pdo = new PDO('mysql:host=127.0.0.1;dbname=statistics','root','zqVnUy6cxsQXbpEZ');
$pdo->exec('set names utf8');
$system_version_sql = "SELECT system_version FROM statistics_device_info GROUP BY system_version";
$system_version_data = $pdo->query($system_version_sql)->fetchAll(PDO::FETCH_ASSOC);
$p = isset($_GET['p'])?$_GET['p']:1;
$size = 1;
if ($p == 1) {
    $limit = 0;
}else{
    $limit = ($p-1)*$size;
}

if (isset($_GET['binding']) && $_GET['binding']) {
    $binding = $_GET['binding'];
}
if (isset($_GET['system_version']) && $_GET['system_version']) {
    $system_version = $_GET['system_version'];
}
if (isset($_GET['device_sn']) && $_GET['device_sn']) {
    $device_sn = $_GET['device_sn'];
}
if (isset($_GET['tel']) && $_GET['tel']) {
    $tel = $_GET['tel'];
}
if (isset($_GET['stats']) && $_GET['stats']) {
    $stats = $_GET['stats'];
}
if (isset($_GET['end']) && $_GET['end']) {
    $end = $_GET['end'];
}
$sql = "SELECT * FROM statistics_device_info WHERE 1 = 1";
if (isset($binding)) {
    if ($binding == '1') {
        $sql .= " AND binding_tel != ''";
    }else{
        $sql .= " AND binding_tel = ''";
    }
}
if (isset($system_version)) {
    $sql .= " AND system_version = '$system_version'";
}
if (isset($device_sn)) {
    $sql .= " AND device_sn like '%$device_sn%'";
}
if (isset($tel)) {
    $sql .= " AND binding_tel like '%$tel%'";
}
if (isset($stats)) {
    $sql .= " AND last_login_time > '$stats'";
}
if (isset($end)) {
    $sql .= " AND last_login_time < '$end'";
}
$device_info = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$sql .= " limit $limit,$size";
$device_data = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$count_sql = "SELECT count(*) FROM statistics_device_info";
$count = $pdo->query($count_sql)->fetch(PDO::FETCH_ASSOC);
$pcount = ceil(count($device_info)/$size);
 ?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>设备信息统计</title>

<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="css/datepicker3.css" rel="stylesheet">
<link href="css/styles.css" rel="stylesheet">

</head>

<body>
    <nav class="navbar navbar-inverse navbar-fixed-top" role="navigation">
        <div class="container-fluid">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#sidebar-collapse">
                    <span class="sr-only">Toggle navigation</span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                <a class="navbar-brand" href=""><span>数据统计</span>嗨镜</a>
                <ul class="user-menu">
                    <li class="dropdown pull-right">
                        <a href="#" class="dropdown-toggle" data-toggle="dropdown"><span class="glyphicon glyphicon-user"></span> User <span class="caret"></span></a>
                        <ul class="dropdown-menu" role="menu">
                            <li><a href="#"><span class="glyphicon glyphicon-log-out"></span> 退出登陆</a></li>
                        </ul>
                    </li>
                </ul>
            </div>
        </div><!-- /.container-fluid -->
    </nav>
        
    <div id="sidebar-collapse" class="col-sm-3 col-lg-2 sidebar">
        <ul class="nav menu">
           <li><a href="index.php"><span class="glyphicon glyphicon-dashboard"></span> 设备日活统计</a></li>
            <li><a href="application_index.php"><span class="glyphicon glyphicon-dashboard"></span> 应用日活统计</a></li>
            <li><a href="device_use_duration.php"><span class="glyphicon glyphicon-th"></span> 设备使用时长统计</a></li>
            <li><a href="application_use_duration.php"><span class="glyphicon glyphicon-th"></span> 应用使用时长统计</a></li>
            <li class="active"><a href="device_info.php"><span class="glyphicon glyphicon-stats"></span> 设备信息统计</a></li>
            <li><a href="user_info.php"><span class="glyphicon glyphicon-list-alt"></span> 用户信息统计</a></li>
            <li><a href="application_info.php"><span class="glyphicon glyphicon-pencil"></span> 应用信息统计</a></li>
            <li><a href="device_time_interval.php"><span class="glyphicon glyphicon-info-sign"></span> 设备日均时段统计</a></li>
            <li><a href="application_time_interval.php"><span class="glyphicon glyphicon-info-sign"></span> 应用日均时段统计</a></li>
        </ul>
        <div class="attribution">More Templates <a href="http://www.cssmoban.com/" target="_blank" title="模板之家">模板之家</a> - Collect from <a href="http://www.cssmoban.com/" title="网页模板" target="_blank">网页模板</a></div>
    </div><!--/.sidebar-->
        
    <div class="col-sm-9 col-sm-offset-3 col-lg-10 col-lg-offset-2 main">   
        <form action="">
            <b>绑定状态</b><select style="width:120px;height:25px;margin-top:30px;border-color:green;" name="binding" id="binding">
                <option value=""><?php echo isset($binding)?$binding==1?'已绑定':'未绑定':"---请选择状态---" ?></option>
                <option value="1">已绑定</option>
                <option value="2">未绑定</option>
                <option value="">---请选择状态---</option>
            </select>
            

            <b>系统版本：</b><select style="width:120px;height:25px;margin-top:30px;border-color:green;" name="system_version" id="system_version">
                <option value=""><?php echo isset($system_version)?$system_version:"---请选择版本---" ?></option>
                <?php foreach ($system_version_data as $key => $value) { ?>
                    <option value="<?php echo $value['system_version'] ?>"><?php echo $value['system_version'] ?></option>
                <?php } ?>
                <option value="">---请选择版本---</option>
            </select>
            
            
            <b>设备码：</b><input name="device_sn" id="device_sn" value="<?php echo isset($device_sn)?$device_sn:'' ?>" type="text" style="width:120px;margin-top:30px;border:2;border-color:green;" placeholder="设备码">
            <b>用户手机：</b><input name="tel" id="tel" value="<?php echo isset($tel)?$tel:'' ?>" type="text" style="width:120px;margin-top:30px;border:2;border-color:green;" placeholder="手机号">
            <b>最后登入时间：</b><input name="stats" id="stats" value="<?php echo isset($stats)?$stats:'' ?>" type="text" style="width:120px;margin-top:30px;border:2;border-color:green;" placeholder="开始时间">
            <input name="end" id="end" value="<?php echo isset($end)?$end:'' ?>" type="text" style="width:120px;margin-top:30px;border:2;border-color:green;" placeholder="结束时间">
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="submit" style="width:120px;border:2;border-color:yellow;background-color:yellow;" value="搜索">
        </form>
        <br>
        <table border="1" style="width: 100%;height: 80%">
            <tr style="height: 50px">
                <td align="center">总激活数</td>
                <td align="center">全部条目</td>
                <td align="center">占比</td>
            </tr>
            <tr style="height: 50px">
                <td align="center"><?php echo count($device_info) ?></td>
                <td align="center"><?php echo $count['count(*)'] ?></td>
                <td align="center"><?php echo round(count($device_info)/$count['count(*)']*100,2)."%" ?></td>
            </tr>
        </table>
        <br>
        <table border="1" style="width: 100%;height: 80%">
            <tr style="height: 30px">
                <td align="center">设备码</td>
                <td align="center">绑定手机</td>
                <td align="center">最后登入时间</td>
                <td align="center">系统版本</td>
            </tr>
            <?php foreach ($device_data as $key => $value) {  ?>
                <tr style="height: 30px">
                    <td align="center"><?php echo $value['device_sn'] ?></td>
                    <td align="center"><?php echo $value['binding_tel'] ?></td>
                    <td align="center"><?php echo date("Y-m-d H:i:s",$value['last_login_time']) ?></td>
                    <td align="center"><?php echo $value['system_version'] ?></td>
                </tr>
            <?php } ?>
        </table>
         <br>
        <center>
        <b>当前页码数 <span id="p" style="color: red"><?php echo $p; ?></span></b>
        <button id="first">首页</button>
        <button id="upper">上一页</button>
        <button id="lower">下一页</button>
        <button id="tail">尾页</button>
        <b>当前总页数 <span id="pcount" style="color:red"><?php echo $pcount ?></span></b>
        </center>
	</div>	<!--/.main-->

	<script src="js/jquery-1.11.1.min.js"></script>
	<script src="js/bootstrap.min.js"></script>
	<script src="js/chart.min.js"></script>
	<script src="js/chart-data.js"></script>
	<script src="js/easypiechart.js"></script>
	<script src="js/easypiechart-data.js"></script>
	<script type="text/javascript" src="./js/echarts.min.js"></script>
	<script src="js/bootstrap-datepicker.js"></script>	
</body>

</html>
<script>
    $("#first").click(function() {
        var end = $("#end").val();
        var tel = $("#tel").val();
        var stats = $("#stats").val();
        var binding = $("#binding").val();
        var device_sn = $("#device_sn").val();
        var system_version = $("#system_version").val();
        window.location.href='./device_info.php?binding='+binding+'&system_version='+system_version+'&device_sn='+device_sn+'&tel='+tel+'&stats='+stats+'&end='+end;
    })

     $("#upper").click(function() {
        var p = parseInt($("#p").html())-parseInt(1);
        if (p < "1") {
            alert("当前属于第一页");
            return false;
        }
        var end = $("#end").val();
        var tel = $("#tel").val();
        var stats = $("#stats").val();
        var binding = $("#binding").val();
        var device_sn = $("#device_sn").val();
        var system_version = $("#system_version").val();
        window.location.href='./device_info.php?binding='+binding+'&system_version='+system_version+'&device_sn='+device_sn+'&tel='+tel+'&stats='+stats+'&end='+end+'&p='+p;
    })

    $("#lower").click(function() {
        var p = parseInt($("#p").html())+parseInt(1);
        var pcount = parseInt($("#pcount").html());
        if (p > pcount) {
            alert("当前属于尾页");
            return false;
        }
        var end = $("#end").val();
        var tel = $("#tel").val();
        var stats = $("#stats").val();
        var binding = $("#binding").val();
        var device_sn = $("#device_sn").val();
        var system_version = $("#system_version").val();
        window.location.href='./device_info.php?binding='+binding+'&system_version='+system_version+'&device_sn='+device_sn+'&tel='+tel+'&stats='+stats+'&end='+end+'&p='+p;
    })

     $("#tail").click(function() {
        var p = $("#pcount").html();
         var end = $("#end").val();
        var tel = $("#tel").val();
        var stats = $("#stats").val();
        var binding = $("#binding").val();
        var device_sn = $("#device_sn").val();
        var system_version = $("#system_version").val();
        window.location.href='./device_info.php?binding='+binding+'&system_version='+system_version+'&device_sn='+device_sn+'&tel='+tel+'&stats='+stats+'&end='+end+'&p='+p;
    })
</script>
